<?php

include "../../../lib/start.php";

check_session();
check_permission(DOC_PERM);

if((!$_SESSION['__user__']->isCoordinator($_SESSION['__classe__']->get_ID())) && (!$_SESSION['__user__']->isAdministrator()) ){
	header("Location: no_permission.php");
}

if(isset($_REQUEST['q']))
	$q = $_REQUEST['q'];
else
	$q = 0;

switch($q){
	case 0:
		$par_tot = "AND data <= NOW()";
		break;
	case 1:
		$par_tot = "AND DATA <= '".format_date($_SESSION['__current_year__']->get_fine_quadrimestre(), IT_DATE_STYLE, SQL_DATE_STYLE, "-")."'";
		break;
	case 2:
		$par_tot = "AND (data BETWEEN '".format_date($_SESSION['__current_year__']->get_fine_quadrimestre(), IT_DATE_STYLE, SQL_DATE_STYLE, "-")."' AND NOW()) ";
}

$sel_totali = "SELECT SEC_TO_TIME(SUM(TIME_TO_SEC((TIMEDIFF((CASE WHEN uscita > '13:30:00' THEN SEC_TO_TIME(TIME_TO_SEC(uscita) -3600) ELSE uscita END), ingresso))))) AS ore, SEC_TO_TIME(SUM(TIME_TO_SEC((TIMEDIFF((CASE WHEN uscita > '13:30:00' THEN SEC_TO_TIME(TIME_TO_SEC(uscita) -3600) ELSE uscita END), ingresso))))/4) AS limite_ore, COUNT(data) AS giorni, FLOOR(COUNT(data)/4) AS limite_giorni FROM reg_classi WHERE id_classe = ".$_SESSION['__classe__']->get_ID()." AND id_anno = ".$_SESSION['__current_year__']->get_ID()." $par_tot";
//print $sel_totali;
$res_totali = $db->execute($sel_totali);
$totali = $res_totali->fetch_assoc();
list($ore, $minuti, $secondi) = explode(":", $totali['ore']);
list($ore2, $minuti2, $secondi2) = explode(":", $totali['limite_ore']);
$sel_assenze_alunni = "SELECT alunni.id_alunno, cognome, nome, SEC_TO_TIME(SUM(TIME_TO_SEC((TIMEDIFF((CASE WHEN (reg_alunni.uscita > '14:31:00') THEN SEC_TO_TIME(TIME_TO_SEC(reg_alunni.uscita) -3600) WHEN reg_alunni.uscita < '13:30:00' THEN reg_alunni.uscita ELSE '13:30:00' END), reg_alunni.ingresso))))) AS ore, TIMEDIFF('".$totali['ore']."', SEC_TO_TIME(SUM(TIME_TO_SEC((TIMEDIFF((CASE WHEN (reg_alunni.uscita > '14:31:00') THEN SEC_TO_TIME(TIME_TO_SEC(reg_alunni.uscita) -3600) WHEN reg_alunni.uscita < '13:30:00' THEN reg_alunni.uscita ELSE '13:30:00' END), reg_alunni.ingresso)))))) AS ore_assenza, COUNT(reg_alunni.ingresso) AS giorni FROM reg_classi, reg_alunni, alunni WHERE reg_classi.id_classe = ".$_SESSION['__classe__']->get_ID()." AND id_anno = ".$_SESSION['__current_year__']->get_ID()." $par_tot AND id_reg = id_registro AND reg_alunni.ingresso <> '00:00:00' AND alunni.id_alunno = reg_alunni.id_alunno GROUP BY alunni.id_alunno, cognome, nome ORDER BY cognome, nome";
$res_assenze_alunni = $db->execute($sel_assenze_alunni);

include "stats.html.php";

?>